package com.personal.summaryofproject.utils.poi;

import com.personal.summaryofproject.enums.BaseExceptionCode;
import com.personal.summaryofproject.exception.BaseException;
import com.personal.summaryofproject.utils.poi.bean.FileDataBean;
import com.personal.summaryofproject.utils.poi.bean.SheetDataBean;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.concurrent.atomic.AtomicBoolean;

/**
 * description: GetExcelFileDataUtil <br>
 * date: 2020/4/18 23:15 <br>
 * author: wenjun.dai <br>
 * version: 1.0 <br>
 */
@Slf4j
public class GetExcelFileDataUtil {

    /**
     * 获取文件中所有 sheet 页数据
     *
     * @param file
     * @return
     */
    public static FileDataBean getFileDataAll(File file, List<String> insteadColumnList) {
        Workbook wb;
        wb = readExcel(file);
        FileDataBean fileDataBean = new FileDataBean();
        if (wb != null) {
            int numberOfSheets = wb.getNumberOfSheets();
            fileDataBean.setSheetCount(numberOfSheets);
            List<SheetDataBean> sheetDataBeanList = new ArrayList<>();
            List<String> sheetNameList = new ArrayList<>();
            for (int index = 0; index < numberOfSheets; index++) {
                // 根据索引获取 sheet 页数据
                SheetDataBean dataFromSheet = getSheetDataFromWorkBook(wb, index,insteadColumnList);
                sheetDataBeanList.add(dataFromSheet);
                sheetNameList.add(dataFromSheet.getSheetName());
            }
            fileDataBean.setSheetDataBeanList(sheetDataBeanList);
            fileDataBean.setSheetNameList(sheetNameList);
        }
        return fileDataBean;
    }

    /**
     * 根据索引,获取指定 sheet 页数据
     *
     * @param file
     * @param index
     * @param insteadColumnList
     * @return
     */
    public static SheetDataBean getSheetDataByIndex(File file, Integer index, List<String> insteadColumnList) {
        Workbook wb;
        wb = readExcel(file);
        SheetDataBean sheetDataBean = null;
        if (wb != null) {
            // 根据索引获取 sheet 页数据
            sheetDataBean = getSheetDataFromWorkBook(wb, index, insteadColumnList);
        }
        return sheetDataBean;
    }

    /**
     * 根据名称,获取指定 sheet 页数据
     *
     * @param file
     * @param sheetName
     * @return
     */
    public static SheetDataBean getSheetDataBySheetName(File file, String sheetName, List<String> insteadColumnList) {
        Workbook wb;
        wb = readExcel(file);
        SheetDataBean sheetDataBean = null;
        if (wb != null) {
            // 根据索引获取 sheet 页数据
            sheetDataBean = getSheetDataFromWorkBook(wb, sheetName,insteadColumnList);
        }
        return sheetDataBean;
    }

    /**
     * 根据名称获取 sheet 页数据
     *
     * @param wb
     * @param sheetName
     * @return
     */
    public static SheetDataBean getSheetDataFromWorkBook(Workbook wb, String sheetName, List<String> insteadColumnList) {
        Sheet sheet = wb.getSheet(sheetName);
        if (null == sheet) {
            String msg = "未获取到名称为 >>" + sheetName + "<< 的 sheet 页!";
            throw new BaseException(msg);
        }
        return getDataFromSheet(sheet, insteadColumnList);
    }

    /**
     * 根据索引获取 sheet 页数据
     *
     * @param wb
     * @param index
     * @param insteadColumnList
     * @return
     */
    public static SheetDataBean getSheetDataFromWorkBook(Workbook wb, Integer index, List<String> insteadColumnList) {
        Sheet sheet = wb.getSheetAt(index);
        if (null == sheet) {
            String msg = "未获取到索引为 >>" + index + "<< 的 sheet 页!";
            throw new BaseException(msg);
        }
        SheetDataBean dataFromSheet = getDataFromSheet(sheet, insteadColumnList);
        dataFromSheet.setIndex(index);
        return dataFromSheet;
    }


    /**
     * 获取给定 sheet 页中数据
     *
     * @param sheet
     * @param insteadColumnList
     * @return
     */
    public static SheetDataBean getDataFromSheet(Sheet sheet, List<String> insteadColumnList) {
        SheetDataBean sheetDataBean = new SheetDataBean();
        // 存放列名
        List<String> columnList = new ArrayList<>();
        //用来存放表中数据
        List<Map<String, String>> sheetDataList = new ArrayList<>();
        String sheetName = sheet.getSheetName();
        sheetDataBean.setSheetName(sheetName);

        //获取最大行数
        int maxRowNum = sheet.getPhysicalNumberOfRows();
        if (maxRowNum < 1) {
            //空文件
            throw new BaseException(BaseExceptionCode.FILE_EMPTY_EXCEPTION);
        }
        //获取第一行
        Row row = sheet.getRow(0);
        if (null == row) {
            throw new BaseException("文件第一行为空!");
        }
        //获取最大列数
        int columnCount = row.getPhysicalNumberOfCells();

        // 遍历单元格获取数据
        for (int i = 0; i < maxRowNum; i++) {
            Map<String, String> rowDataMap = new HashMap<>();
            row = sheet.getRow(i);
            if (row != null) {
                for (int j = 0; j < columnCount; j++) {
                    //  获取单元格内容
                    String cellData = getCellFormatValue(row.getCell(j));
                    if (i == 0) {
                        // 第一行,列名作为后续的 key
                        if (null == cellData || cellData.isEmpty()) {
                            columnCount = j;
                            break;
                        }
                        columnList.add(cellData);
                    } else {
                        if (null != insteadColumnList && insteadColumnList.size() == columnList.size()) {
                            rowDataMap.put(insteadColumnList.get(j), cellData);
                        } else if (null != insteadColumnList && insteadColumnList.size() != columnList.size()) {
                            throw new BaseException("列名对应别名数量不一致!");
                        } else {
                            // 数据行
                            rowDataMap.put(columnList.get(j), cellData);
                        }
                    }
                }
            } else {
                break;
            }
            // 判断是否为空数据行
            AtomicBoolean rowIsEmpty = new AtomicBoolean(true);
            rowDataMap.values().forEach(value -> {
                if (null != value && !value.isEmpty()) {
                    rowIsEmpty.set(false);
                }
            });
            if (!rowIsEmpty.get()) {
                sheetDataList.add(rowDataMap);
            }
        }
        sheetDataBean.setFirstColumnList(columnList);
        sheetDataBean.setSheetDataList(sheetDataList);
        sheetDataBean.setInsteadColumnList(insteadColumnList);
        return sheetDataBean;
    }


    /**
     * 读取 Excel 文件
     *
     * @param file
     * @return
     */
    private static Workbook readExcel(File file) {
        if (file == null) {
            return null;
        }
        String name = file.getName();
        String extString = name.substring(name.lastIndexOf("."));
        InputStream is;
        try {
            is = new FileInputStream(file);
            if (".xls".equals(extString)) {
                return new HSSFWorkbook(is);
            } else if (".xlsx".equals(extString)) {
                return new XSSFWorkbook(is);
            } else {
                return null;
            }

        } catch (FileNotFoundException e) {
            log.error("未找到对应的文件!", e);
        } catch (IOException e) {
            log.error("生成 Workbook 异常!", e);
        }
        return null;
    }

    /**
     * 获取单元格内容
     *
     * @param cell
     * @return
     */
    public static String getCellFormatValue(Cell cell) {
        String cellValue;
        //数字不保留小数
        DecimalFormat df = new DecimalFormat("#");
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        try {
            if (cell != null) {
                //判断cell类型
                switch (cell.getCellType()) {
                    // 数字格式
                    case Cell.CELL_TYPE_NUMERIC: {
                        //判断cell是否为日期格式
                        if (DateUtil.isCellDateFormatted(cell)) {
                            //转换为日期格式YYYY-mm-dd
                            cellValue = sdf.format(cell.getDateCellValue());
                        } else {
                            //数字
                            cellValue = String.valueOf(df.format(cell.getNumericCellValue()));
                        }
                        break;
                    }
                    // 表达式
                    case Cell.CELL_TYPE_FORMULA:
                        //字符串
                    case Cell.CELL_TYPE_STRING: {
                        cellValue = cell.getRichStringCellValue().getString().trim();
                        break;
                    }
                    default:
                        cellValue = "";
                }
            } else {
                cellValue = "";
            }
        } catch (Exception e) {
            log.error("单元格内容解析错误!", e);
            throw new BaseException("单元格内容解析错误!");
        }
        return cellValue;
    }

}
